package work.mediway.mdm.biz.service.biz.impl;

import com.baomidou.mybatisplus.core.toolkit.StringPool;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.mediway.hos.common.utils.spring.SpringUtils;

import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;

import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.text.StrPool;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import work.mediway.mdm.core.helper.BaseDatabaseHelper;
import work.mediway.mdm.core.vo.TableVO;
import work.mediway.mdm.biz.service.biz.SsModelInfoService;
import work.mediway.mdm.biz.service.biz.TableModelService;
import work.mediway.mdm.core.constant.MdmConstant;
import work.mediway.mdm.core.entity.Column;
import work.mediway.mdm.core.entity.IndexInfo;
import work.mediway.mdm.core.entity.Table;
import work.mediway.mdm.core.entity.TableForeignKey;
import work.mediway.mdm.core.enums.DataFormatEnum;
import work.mediway.mdm.core.enums.StatusWhetherEnum;
import work.mediway.mdm.core.exception.DataException;
import work.mediway.mdm.core.transport.ErrorMsgVO;
import work.mediway.mdm.biz.entity.biz.SsModelInfo;
import work.mediway.mdm.biz.entity.dict.CtCodeSystem;
import work.mediway.mdm.biz.helper.BusinessDatabaseHelper;
import work.mediway.mdm.biz.service.biz.SsRoleLinkTableService;
import work.mediway.mdm.biz.service.common.CommonDictService;
import work.mediway.mdm.biz.service.dict.CtCodeSystemService;
import work.mediway.mdm.biz.vo.dict.DictFieldVO;

/**
 * @author zhongyj <1126834403@qq.com><br/>
 * @date 2022/6/30
 */
@Slf4j
@Service
@Transactional(rollbackFor = Exception.class, propagation = Propagation.REQUIRED)
@RequiredArgsConstructor
public class TableModelServiceImpl implements TableModelService {

    private final JdbcTemplate jdbcTemplate;
    private final BaseDatabaseHelper baseDatabaseHelper;
    private final BusinessDatabaseHelper businessDatabaseHelper;
    private final CtCodeSystemService codeSystemService;
    private final SsRoleLinkTableService roleLinkTableService;

    @Transactional(rollbackFor = Exception.class, propagation = Propagation.REQUIRED)
    @Override
    public void createTable(Table table) {
        // 先清除缓存
        businessDatabaseHelper.cleanTableCache(table.getTableName());
        List<TableVO> tableVo = this.baseDatabaseHelper.getTable(table.getTableName());
        if (CollUtil.isNotEmpty(tableVo)) {
            throw new DataException(StrUtil.format("数据库表 {} 已存在, 新增失败", table.getTableName()));
        }
        // 分行存储建表语句
        List<String> columnLine = CollUtil.newArrayList();
        columnLine.add("CREATE TABLE " + table.getTableName() + " ( ");
        // 存储字段、主键、外键、索引列
        List<String> col = CollUtil.newArrayList();
        // 构造列
        buildColumn(table.getColumns(), col);
        // 去除末尾多余的,
        columnLine.add(StrUtil.removeSuffix(StrUtil.join(StrPool.LF, col), StrPool.COMMA));
        columnLine.add(") ENGINE=InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC COMMENT='" + table.getComment() + "';");

        String sql = StrUtil.join(StrPool.LF, columnLine);
        // 创建表
        jdbcTemplate.execute(sql);
        // 管理员用户每张表自动分配所有权限
        this.roleLinkTableService.autoAddPermForAdmin(this.baseDatabaseHelper.getTable(table.getTableName()).get(0));
        // 生成表信息
        CtCodeSystem codeSystem = new CtCodeSystem();
        codeSystem.setCodeSystemCode(table.getTableName());
        codeSystem.setCodeSystemDesc(table.getComment());
        this.codeSystemService.saveCodeSystem(codeSystem);
        // 生成消息格式
        SsModelInfo ssModelInfo = new SsModelInfo();
        ssModelInfo.setModelCode(table.getTableName());
        ssModelInfo.setModelName(table.getComment());
        ssModelInfo.setModelType("2");
        ssModelInfo.setSavePath("/dict/general");
        ssModelInfo.setSaveMethod("POST");
        ssModelInfo.setSaveParamType(DataFormatEnum.JSON.name());
        ssModelInfo.setSaveRequest(JSONUtil.toJsonPrettyStr(SpringUtils.getBean(CommonDictService.class).buildDictTemplate(table.getTableName(), DataFormatEnum.JSON).getInput().getDataFormat()));
        ssModelInfo.setSaveResponse(JSONUtil.toJsonPrettyStr(SpringUtils.getBean(CommonDictService.class).buildDictTemplate(table.getTableName(), DataFormatEnum.JSON).getInput().getSuccess()));

        ssModelInfo.setSelectPath("/dict/general");
        ssModelInfo.setSelectMethod("GET");
        ssModelInfo.setSelectParamType(DataFormatEnum.JSON.name());
        ssModelInfo.setSelectRequest(JSONUtil.toJsonPrettyStr(SpringUtils.getBean(CommonDictService.class).buildDictTemplate(table.getTableName(), DataFormatEnum.JSON).getOutput().getParam()));
        ssModelInfo.setSelectResponse(JSONUtil.toJsonPrettyStr(SpringUtils.getBean(CommonDictService.class).buildDictTemplate(table.getTableName(), DataFormatEnum.JSON).getOutput().getSuccess()));
        SpringUtils.getBean(SsModelInfoService.class).save(ssModelInfo);
    }

    private void buildColumn(List<Column> columns, List<String> col) {
        List<TableForeignKey> foreignKeys = CollUtil.newArrayList();
        for (Column column : columns) {
            StringBuilder columnStr = StrUtil.builder().append(StringPool.TAB).append("`").append(column.getColumnName()).append("` ");
            columnStr.append(column.getColumnType());
            if (StrUtil.isNotBlank(column.getColumnLength())) {
                columnStr.append("(").append(column.getColumnLength()).append(")");
            }
            columnStr.append(" ")
                    .append(column.getIsNotNull() ? "NOT NULL" : "NULL").append(" ")
                    .append(column.getIsIncrement() ? "AUTO_INCREMENT" : "").append(" ")
                    .append(column.getIsPrimaryKey() ? "PRIMARY KEY" : "").append(" ")
                    .append("COMMENT").append(" '").append(column.getComment()).append("'").append(",");
            col.add(columnStr.toString());
            if (ObjectUtil.isNotEmpty(column.getFkTableName())) {
                TableForeignKey tableForeignKey = new TableForeignKey();
                tableForeignKey.setFkColumnName(column.getColumnName());
                tableForeignKey.setPkTableName(column.getFkTableName());
                foreignKeys.add(tableForeignKey);
            }
        }
        for (TableForeignKey foreignKey : foreignKeys) {
            List<String> primaryKey = this.businessDatabaseHelper.getPrimaryKey(foreignKey.getPkTableName());
            col.add(StrUtil.builder().append(StringPool.TAB).append("FOREIGN KEY (`")
                    .append(foreignKey.getFkColumnName()).append("`) ").append("REFERENCES ").append(foreignKey.getPkTableName())
                    .append("(`").append(primaryKey.get(0)).append("`)").append(",").toString());
        }
    }

    @Transactional(rollbackFor = Exception.class, propagation = Propagation.REQUIRED)
    @Override
    public ErrorMsgVO deleteTable(List<String> tableName) {
        ErrorMsgVO msg = new ErrorMsgVO();
        for (String name : tableName) {
            // 先清除缓存
            businessDatabaseHelper.cleanTableCache(name);
            try {
                Integer count = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM " + name, Integer.class);
                if (count != null && count > 0) {
                    msg.setErrorMsg(name, "表中存在数据, 不允许删除");
                    continue;
                }
                // 删除codeSystem表中信息
                this.codeSystemService.remove(Wrappers.lambdaUpdate(CtCodeSystem.class).eq(CtCodeSystem::getCodeSystemCode, name));
                String dropTable = "DROP TABLE IF EXISTS " + name;
                // 删除表
                jdbcTemplate.execute(dropTable);
            } catch (Exception e) {
                msg.setErrorMsg(name, e.getMessage());
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            }
        }
        msg.setCount(tableName.size());
        return msg;
    }

    @Override
    public Table selectTable(String tableName) {
        TableVO tableVo = this.baseDatabaseHelper.getTable(tableName).get(0);
        List<DictFieldVO> fields = this.businessDatabaseHelper.getColumn(tableName, true);
        Table table = new Table();
        table.setTableName(tableVo.getTableCode());
        table.setComment(tableVo.getTableName());
        for (DictFieldVO field : fields) {
            Column column = new Column();
            column.setColumnName(field.getFieldCode());
            column.setColumnType(field.getSqlType());
            column.setColumnLength(field.getColumnSize());
            column.setIsPrimaryKey(StrUtil.equalsAnyIgnoreCase(field.getPrimaryKey(), StatusWhetherEnum.STATUS_Y.getStatusCode()));
            column.setIsIncrement(StrUtil.equalsAnyIgnoreCase(field.getIsAutoIncrement(), StatusWhetherEnum.STATUS_YES.getStatusCode()));
            column.setIsNotNull(field.getRequired());
            column.setComment(field.getFieldDesc());
            column.setExistForeignKey(StrUtil.isNotBlank(field.getFkTableName()));
            if (column.getExistForeignKey()) {
                column.setFkTableName(field.getFkTableName());
            }
            table.getColumns().add(column);
        }
        return table;
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public void updateTable(Table table) {
        // 先清除缓存
        businessDatabaseHelper.cleanTableCache(table.getTableName());

        Table selectTable = selectTable(table.getTableName());
        // 判断表信息是否修改
        if (!StrUtil.equalsAnyIgnoreCase(table.getComment(), selectTable.getTableName())) {
            String sql = "ALTER TABLE " + table.getTableName() + " COMMENT '" + table.getComment() + "'";
            log.info("更新表信息: {}", sql);
            jdbcTemplate.execute(sql);
        }
        // 判断列情况
        List<String> tableColumnList = selectTable.getColumns().stream().map(Column::getColumnName).collect(Collectors.toList());
        for (Column inputColumn : table.getColumns()) {
            for (Column selectColumn : selectTable.getColumns()) {
                if (StrUtil.equalsAnyIgnoreCase(inputColumn.getColumnName(), selectColumn.getColumnName())) {
                    // 可能是更新或者删除
                    if (StrUtil.isNotBlank(inputColumn.getColumnName()) && StrUtil.isBlank(inputColumn.getColumnType())) {
                        // 如果列名不为空，列注释为空，则为删除，否则为更新
                        String sql = "ALTER TABLE " + table.getTableName() + " DROP COLUMN " + selectColumn.getColumnName();
                        log.info("删除列: {}", sql);
                        jdbcTemplate.execute(sql);
                    } else {
                        // 更新操作
                        List<String> col = CollUtil.newArrayList();
                        buildColumn(CollUtil.newArrayList(inputColumn), col);
                        // 更新
                        for (String sql : col) {
                            if (StrUtil.containsIgnoreCase(sql, "FOREIGN KEY")) {
                                if ((!selectColumn.getExistForeignKey()) || (selectColumn.getExistForeignKey() && !StrUtil.equalsAnyIgnoreCase(selectColumn.getFkTableName(), inputColumn.getFkTableName()))) {
                                    // 新增外键
                                    String exeSql = "ALTER TABLE " + table.getTableName() + " ADD " + StrUtil.removeSuffix(sql, ",");
                                    log.info("添加外键: {}", exeSql);
                                    jdbcTemplate.batchUpdate("SET foreign_key_checks = 0;", exeSql, "SET foreign_key_checks = 1;");
                                } else if (selectColumn.getExistForeignKey() && StrUtil.equalsAnyIgnoreCase(selectColumn.getFkTableName(), inputColumn.getFkTableName())) {
                                    // 如果存在外键，同时传入的外键跟存在的外键相同，不做处理，后续考虑是否删除
                                    break;
                                }
                            } else {
                                // 更新列
                                if (selectColumn.getIsPrimaryKey() && StrUtil.containsIgnoreCase(sql, "PRIMARY KEY")) {
                                    // 说明主键不变，更新其中数据
                                    sql = StrUtil.removeAll(sql, "PRIMARY KEY");
                                    String exeSql = "ALTER TABLE " + table.getTableName() + " MODIFY  " + StrUtil.removeSuffix(StrUtil.removeAny(sql, StrPool.TAB), ",");
                                    log.info("更新列信息: {}", exeSql);
                                    jdbcTemplate.execute(exeSql);
                                } else if (selectColumn.getIsPrimaryKey() && !StrUtil.containsIgnoreCase(sql, "PRIMARY KEY")) {
                                    // 需要删除主键
                                    String exeSql = "ALTER TABLE " + table.getTableName() + " DROP PRIMARY KEY";
                                    log.info("删除主键并更新列: {}", sql);
                                    jdbcTemplate.execute(exeSql);
                                    // 删除主键时，需要将自增改为不自增
                                    sql = StrUtil.removeAll(sql, "AUTO_INCREMENT");
                                    exeSql = "ALTER TABLE " + table.getTableName() + " MODIFY  " + StrUtil.removeSuffix(StrUtil.removeAny(sql, StrPool.TAB), ",");
                                    log.info("删除主键并更新列: {}", exeSql);
                                    jdbcTemplate.execute(exeSql);
                                } else if (!selectColumn.getIsPrimaryKey() && StrUtil.containsIgnoreCase(sql, "PRIMARY KEY")) {
                                    // 新增主键
                                    sql = StrUtil.removeAll(sql, "PRIMARY KEY");
                                    String exeSql = "ALTER TABLE " + table.getTableName() + " MODIFY  " + StrUtil.removeSuffix(StrUtil.removeAny(sql, StrPool.TAB), ",");
                                    log.info("新增主键先新增列: {}", exeSql);
                                    jdbcTemplate.execute(exeSql);
                                    exeSql = "ALTER TABLE " + table.getTableName() + " ADD PRIMARY KEY(" + inputColumn.getColumnName() + ")";
                                    log.info("新增主键先新增列: {}", exeSql);
                                    jdbcTemplate.execute("ALTER TABLE " + table.getTableName() + " ADD PRIMARY KEY(" + inputColumn.getColumnName() + ")");
                                } else {
                                    String exeSql = "ALTER TABLE " + table.getTableName() + " MODIFY  " + StrUtil.removeSuffix(StrUtil.removeAny(sql, StrPool.TAB), ",");
                                    log.info("更新列信息: {}", exeSql);
                                    jdbcTemplate.execute(exeSql);
                                }
                            }
                        }
                    }
                    break;
                } else if (!CollUtil.contains(tableColumnList, inputColumn.getColumnName())) {
                    // 新增列
                    List<String> col = CollUtil.newArrayList();
                    buildColumn(CollUtil.newArrayList(inputColumn), col);
                    for (String sql : col) {
                        if (StrUtil.containsIgnoreCase(sql, "FOREIGN KEY")) {
                            String exeSql = "ALTER TABLE " + table.getTableName() + " ADD CONSTRAINT " + StrUtil.removeSuffix(sql, ",");
                            log.info("新增外键信息: {}", exeSql);
                            jdbcTemplate.batchUpdate("SET foreign_key_checks = 0;", exeSql, "SET foreign_key_checks = 1;");
                        } else {
                            String exeSql = "ALTER TABLE " + table.getTableName() + " ADD " + StrUtil.removeSuffix(StrUtil.removeAny(sql, StrPool.TAB), ",");
                            log.info("新增列信息: {}", exeSql);
                            jdbcTemplate.execute(exeSql);
                        }
                    }
                    break;
                }
            }
        }
        CtCodeSystem codeSystem = new CtCodeSystem();
        codeSystem.setCodeSystemCode(table.getTableName());
        codeSystem.setCodeSystemDesc(table.getComment());
        this.codeSystemService.updateCodeSystemByCode(codeSystem);
    }

    @Override
    public List<IndexInfo> selectTableIndexInfo(String tableName) {
        List<IndexInfo> indexInfo = this.businessDatabaseHelper.getIndexInfo(tableName);
        List<IndexInfo> indexInfoResult = CollUtil.newArrayList();
        Map<String, IndexInfo> indexInfoMap = MapUtil.newHashMap();
        List<String> indexNames = CollUtil.newArrayList();
        indexInfo.forEach(item -> {
            if (CollUtil.contains(indexNames, item.getIndexName())) {
                IndexInfo info = indexInfoMap.get(item.getIndexName());
                info.setColumnName(info.getColumnName() + "," + item.getColumnName());
                info.getColumnNames().add(item.getColumnName());
            } else {
                item.getColumnNames().add(item.getColumnName());
                indexInfoMap.put(item.getIndexName(), item);
                indexNames.add(item.getIndexName());
            }
        });
        indexInfoMap.forEach((k, v) -> indexInfoResult.add(v));
        return indexInfoResult;
    }

    @Override
    public ErrorMsgVO saveTableIndexInfo(List<IndexInfo> indexInfo) {
        ErrorMsgVO msgVO = new ErrorMsgVO();
        for (IndexInfo info : indexInfo) {
            // 先清除缓存
            businessDatabaseHelper.cleanTableCache(info.getTableName());
            try {
                if (StrUtil.isBlank(info.getIndexName())) {
                    info.setIndexName(StrUtil.join("_", info.getColumnNames()));
                }
                List<String> cols = CollUtil.newArrayList();
                for (String columnName : info.getColumnNames()) {
                    if (!StrUtil.containsAnyIgnoreCase(columnName, "`")) {
                        cols.add(StrUtil.builder().append("`").append(columnName).append("`").toString());
                    }
                }
                info.setColumnName(StrUtil.join(",", cols));
                jdbcTemplate.execute(StrUtil.format("ALTER TABLE {} ADD INDEX {} ({})", info.getTableName(), info.getIndexName(), info.getColumnName()));
            } catch (Exception e) {
                msgVO.setErrorMsg(info, "删除索引失败: " + e.getMessage());
            }
        }
        msgVO.setCount(indexInfo.size());
        return msgVO;
    }

    @Override
    public ErrorMsgVO deleteTableIndexInfo(IndexInfo.IndexInfoDTO indexName) {
        ErrorMsgVO msgVO = new ErrorMsgVO();
        for (String name : indexName.getIndexNames()) {
            // 先清除缓存
            businessDatabaseHelper.cleanTableCache(indexName.getTableName());
            String sql = StrUtil.format("DROP INDEX {} ON {}", name, indexName.getTableName());
            try {
                jdbcTemplate.execute(sql);
            } catch (Exception e) {
                msgVO.setErrorMsg(sql, "删除索引失败: " + e.getMessage());
            }
        }
        msgVO.setCount(indexName.getIndexNames().size());
        return msgVO;
    }
}
